﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for ItemDAO
/// </summary>
public class ItemDAO:DBConnect
{
    string SQL_SELECT_ITEM = "select * from items";
    string SQL_GetItem_by_CategoryID = "select * from items where category_id = @ID";
    string SQL_GetItem_by_ID = "select * from items where item_id =@IDITEM";
	public ItemDAO()
	{		
	}
    public IList<Item> GetItems()
    {
        IList<Item> list = new List<Item>();
            try
            {
                SqlCommand cmd = new SqlCommand(SQL_SELECT_ITEM, conn);
                if (ConnectDB())
                {

                    SqlDataReader rd = cmd.ExecuteReader();
                    while (rd.Read())
                    {
                        Item item = new Item();
                        item.Item_id = rd.GetInt32(0);
                        item.Category_id = rd.GetInt32(1);
                        item.Name = rd.GetString(2);
                        item.Author = rd.GetString(3);
                        item.Price = rd.GetDecimal(4);
                        item.Image_url = rd.GetString(5);
                        item.Notes = rd.GetString(6);
                        item.Is_recommended = rd.GetByte(7);
                        item.Rating = rd.GetInt32(8);
                        item.Rating_count = rd.GetInt32(9);

                        list.Add(item);
                    }
                    Disconnect();
                }
            }catch(SqlException e)
            {
                Disconnect();
            }
        return list;
        
    }
    public Item GetItem_by_ID(int ID)
    {
        Item item = new Item();
        
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_GetItem_by_ID, conn);
            cmd.Parameters.AddWithValue("@IDITEM", ID);
            if (ConnectDB())
            {

                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {               
                    item.Item_id = rd.GetInt32(0);
                    item.Category_id = rd.GetInt32(1);
                    item.Name = rd.GetString(2);
                    item.Author = rd.GetString(3);
                    item.Price = rd.GetDecimal(4);
                    item.Image_url = rd.GetString(5);
                    item.Notes = rd.GetString(6);
                    item.Is_recommended = rd.GetByte(7);
                    item.Rating = rd.GetInt32(8);
                    item.Rating_count = rd.GetInt32(9);        
                }
                Disconnect();
            }
        }
        catch (SqlException e)
        {
            Disconnect();
        }
        return item;

    }
    public IList<Item> GetItem_by_CategoryID(int category_ID)
    {
        IList<Item> list = new List<Item>();
            try 
            {
                SqlCommand cmd = new SqlCommand(SQL_GetItem_by_CategoryID, conn);
                cmd.Parameters.AddWithValue("@ID",category_ID);
                if (ConnectDB())
                {

                    SqlDataReader rd = cmd.ExecuteReader();
                    while (rd.Read())
                    {
                        Item item = new Item();
                        item.Item_id = rd.GetInt32(0);
                        item.Category_id = rd.GetInt32(1);
                        item.Name = rd.GetString(2);
                        item.Author = rd.GetString(3);
                        item.Price = rd.GetDecimal(4);
                        item.Image_url = rd.GetString(5);
                        item.Notes = rd.GetString(6);
                        item.Is_recommended = rd.GetByte(7);
                        item.Rating = rd.GetInt32(8);
                        item.Rating_count = rd.GetInt32(9);

                        list.Add(item);
                    }
                    Disconnect();
                }

            }
            catch(SqlException e1)
            {
                Disconnect();
            }
       
        return list;
    }
}